/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.altius.dao.impl;

import cc.altius.dao.SpecialistDao;
import cc.altius.model.AppSpecialist;
import cc.altius.model.Hospital;
import cc.altius.model.Specialist;
import cc.altius.model.SpecialistType;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author santosh
 */
@Repository("SpecialistDao")
public class SpecialistDaoImpl implements SpecialistDao {

    private SessionFactory sessionFactory;
    private HibernateTemplate hibernateTemplate;
    @Autowired
    private DataSource dataSource;
    Session session;

    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
        this.hibernateTemplate = new HibernateTemplate(sessionFactory);
    }

    @Override
    @Transactional
    public void addSpecialist(Specialist specialist) {
        this.hibernateTemplate.save(specialist);
    }

    @Override
    @Transactional
    public List<Specialist> getspecialistList() {
        session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("Specialist.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public List<SpecialistType> getspecialistTypeList() {
        session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("SpecialistType.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public List<Hospital> getHospitalList() {
        session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("Hospital.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public Specialist getSpecialistBySpecialistId(int specialistId) {
        session = this.sessionFactory.getCurrentSession();
        Criteria criteria = session.createCriteria(Specialist.class);
        criteria.add(Restrictions.eq("specialistId", specialistId));
        return (Specialist) criteria.uniqueResult();
    }

    @Override
    @Transactional
    public int updateSpecialist(Specialist specialist) {
        String hql = "update "
                + "Specialist"
                + " set "
                + "name=:name,"
                + " mobile=:mobile,"
                + " email=:email,"
                + " contact=:contact,"
                + " hospital1=:hospital1,"
                + " timing1=:timing1,"
                + " hospital2=:hospital2, "
                + "timing2=:timing2,"
                + " hospital3=:hospital3,"
                + " timing3=:timing3,"
                + " walkin=:walkin,"
                + " hospitalId.hospitalId=:hospitalId,"
                + " specialistTypeId.specialistTypeId=:specialistTypeId"
                + " where specialistId=:specialistId";
        session = this.sessionFactory.getCurrentSession();
        Query query = session.createQuery(hql);

        query.setParameter("name", specialist.getName());
        query.setParameter("mobile", specialist.getMobile());
        query.setParameter("email", specialist.getEmail());
        query.setParameter("contact", specialist.getContact());
        query.setParameter("hospital1", specialist.getHospital1());
        query.setParameter("timing1", specialist.getTiming1());
        query.setParameter("hospital2", specialist.getHospital2());
        query.setParameter("timing2", specialist.getTiming2());
        query.setParameter("hospital3", specialist.getHospital3());
        query.setParameter("timing3", specialist.getTiming3());
        query.setParameter("walkin", specialist.getWalkin());
        query.setParameter("hospitalId", specialist.getHospitalId().getHospitalId());
        query.setParameter("specialistTypeId", specialist.getSpecialistTypeId().getSpecialistTypeId());
        query.setParameter("specialistId", specialist.getSpecialistId());

        int result = query.executeUpdate();
        return result;
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getFilterSpecialist(int specialistTypeId, int zoneId, int hospitalId) {

        String sql = "SELECT  "
                + " specialist.SPECIALIST_ID,"
                + " specialist.NAME,"
                + " specialist_type.SPECIALIST_TYPE,"
                + " specialist.MOBILE,"
                + " master_zone.MASTER_ZONE_CODE,"
                + " zone.ZONE_NAME,"
                + " specialist.CONTACT,specialist.EMAIL,hospital.HOSPITAL_NAME,specialist.TIMING_1 "
                + " FROM specialist LEFT JOIN specialist_type on specialist.SPECIALIST_TYPE_ID=specialist_type.SPECIALIST_TYPE_ID "
                + " LEFT JOIN hospital on specialist.HOSPITAL_ID=hospital.HOSPITAL_ID "
                + " LEFT JOIN zone on hospital.ZONE_ID=zone.ZONE_ID"
                + " LEFT JOIN master_zone on zone.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID WHERE 1 ";

        if (specialistTypeId != 0) {
            sql += " AND specialist_type.SPECIALIST_TYPE_ID=" + specialistTypeId;
        }
        if (zoneId != 0) {
            sql += " AND zone.ZONE_ID=" + zoneId;
        }
        if (hospitalId != 0) {
            sql += " AND hospital.HOSPITAL_ID=" + hospitalId;
        }
        sql +=" ORDER BY hospital.`HOSPITAL_NAME` ASC";
        
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            System.out.println("" + e.getMessage());
            return null;

        }
    }

    @Override
    @Transactional
    public int addAppSpecialist(AppSpecialist appSpecialist) {
        return (Integer) this.hibernateTemplate.save(appSpecialist);
    }

    @Override
    @Transactional
    public List<AppSpecialist> getAppSpecialistList() {
        List<AppSpecialist> appSpecialist = this.hibernateTemplate.find("from AppSpecialist");
        return appSpecialist;
    }

    @Override
    @Transactional
    public AppSpecialist getAppSpecialistByAppSpecialistId(int appSpecialistId) {
        Session session = this.sessionFactory.getCurrentSession();
        Criteria criteria = session.createCriteria(AppSpecialist.class);
        criteria.add(Restrictions.eq("appSpecialistId", appSpecialistId));
        return (AppSpecialist) criteria.uniqueResult();
    }

    @Override
    @Transactional
    public int updateAppSpecialist(AppSpecialist appSpecialist) {
        String hql = "update "
                + "AppSpecialist"
                + " set "
                + " name=:name,"
                + " mobile=:mobile,"
                + " email=:email,"
                + " hospital1=:hospital1,"
                + " contact1=:contact1,"
                + " timing1=:timing1,"
                + " hospital2=:hospital2, "
                + " contact2=:contact2,"
                + " timing2=:timing2,"
                + " hospital3=:hospital3,"
                + " contact3=:contact3,"
                + " timing3=:timing3,"
                + " remarks=:remarks,"
                + " active=:active,"
                + " geographicalZone.geographicalZoneId=:geographicalZoneId,"
                //                + " hospitalId.hospitalId=:hospitalId,"
                + " specialistTypeId.specialistTypeId=:specialistTypeId"
                + " where appSpecialistId=:appSpecialistId";
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.createQuery(hql);

        query.setParameter("name", appSpecialist.getName());
        query.setParameter("mobile", appSpecialist.getMobile());
        query.setParameter("email", appSpecialist.getEmail());
        query.setParameter("hospital1", appSpecialist.getHospital1());
        query.setParameter("contact1", appSpecialist.getContact1());
        query.setParameter("timing1", appSpecialist.getTiming1());
        query.setParameter("hospital2", appSpecialist.getHospital2());
        query.setParameter("contact2", appSpecialist.getContact2());
        query.setParameter("timing2", appSpecialist.getTiming2());
        query.setParameter("hospital3", appSpecialist.getHospital3());
        query.setParameter("contact3", appSpecialist.getContact3());
        query.setParameter("timing3", appSpecialist.getTiming3());
        query.setParameter("remarks", appSpecialist.getRemarks());
        query.setParameter("active", appSpecialist.isActive()); 
//        query.setParameter("hospitalId", appSpecialist.getHospitalId().getHospitalId());
        query.setParameter("geographicalZoneId", appSpecialist.getGeographicalZone().getGeographicalZoneId());
        query.setParameter("specialistTypeId", appSpecialist.getSpecialistTypeId().getSpecialistTypeId());
        query.setParameter("appSpecialistId", appSpecialist.getAppSpecialistId());

        int result = query.executeUpdate();
        return result;
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getFilterAppSpecialist(int specialistTypeId) {
        String sql = " SELECT "
                + " app_specialist.APP_SPECIALIST_ID,"
                + " app_specialist.NAME,"
                + " specialist_type.SPECIALIST_TYPE,"
                + " app_specialist.MOBILE,"
                + " app_specialist.EMAIL,"
                + " app_specialist.HOSPITAL_1, "
                + " app_specialist.CONTACT_1,"
                + " app_specialist.TIMING_1,"
                + " app_specialist.HOSPITAL_2, "
                + " app_specialist.CONTACT_2,"
                + " app_specialist.TIMING_2,"
                + " app_specialist.HOSPITAL_3, "
                + " app_specialist.CONTACT_3,"
                + " app_specialist.TIMING_3,"
                + " app_specialist.REMARKS,"
                //                + "master_zone.MASTER_ZONE_CODE, "
                + " geographical_zone.GEOGRAPHICAL_ZONE_NAME "
                + " FROM app_specialist "
                + " LEFT JOIN specialist_type on app_specialist.SPECIALIST_TYPE_ID=specialist_type.SPECIALIST_TYPE_ID "
                //                + "LEFT JOIN master_zone on app_specialist.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID "
                + " LEFT JOIN geographical_zone on app_specialist.GEOGRAPHICAL_ZONE_ID= geographical_zone.GEOGRAPHICAL_ZONE_ID "
                + " WHERE 1 AND app_specialist.ACTIVE=true";

        if (specialistTypeId != 0) {
            sql += " AND specialist_type.SPECIALIST_TYPE_ID=" + specialistTypeId;
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            System.out.println("" + e.getMessage());
            return null;

        }
    }

    @Override
    @Transactional
    public List<AppSpecialist> AppSpecialistListForAutoComplete(String appSpecialistName) {
        session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
        Criteria cr = session.createCriteria(AppSpecialist.class);
        cr.add(Restrictions.like("name", appSpecialistName, MatchMode.ANYWHERE));
        return cr.list();
    }

    @Override
    @Transactional
    public List<Specialist> SpecialistListForAutoComplete(String specialistName) {
        session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
        Criteria cr = session.createCriteria(Specialist.class);
        cr.add(Restrictions.like("name", specialistName, MatchMode.ANYWHERE));
        return cr.list();
    }
}
